package com.fact.models;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import com.fact.dto.ScenarioDTO;
import com.fact.dto.SegmentDTO;
import com.fact.utils.connection.ConnectionManager;

public class ScenarioService {
	
	
	public ArrayList<ScenarioDTO> getScenarioList()
	{
		ScenarioDTO scenario=null;
		ArrayList<ScenarioDTO> scenarioList=new ArrayList<ScenarioDTO>();
		Connection conn = ConnectionManager.getConnection();
		String sql="Select * from MST_SCENARIO order by POSITION_NO";
		   
		   PreparedStatement stmt = null;
		   ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				r = stmt.executeQuery();
				while (r.next())
				{
					scenario=new ScenarioDTO();
					scenario.setScenarioId(r.getInt("SCENARIO_ID"));
					scenario.setScenarioName(r.getString("SCENARIO_NAME"));
					scenario.setPositionNo(r.getInt("POSITION_NO"));
					scenarioList.add(scenario);
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
		
		return scenarioList;
	}
	
	public boolean saveScenario(ScenarioDTO scenario)
	{

		Connection conn = ConnectionManager.getConnection();
		String sql=" Insert into MST_SCENARIO(SCENARIO_ID,SCENARIO_NAME,POSITION_NO) " +
				   " values(SQN_SCENARIO.nextval,?,?)";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setString(1,scenario.getScenarioName());
				stmt.setInt(2,scenario.getPositionNo());
				
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 	if(response==1)
	 		return true;
	 	else
	 		return false;
	}
	
	public boolean deleteScenario(int scenarioId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Delete MST_SCENARIO Where SCENARIO_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);
				stmt.setInt(1,scenarioId);
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public boolean editScenario(ScenarioDTO scenario)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Update MST_SCENARIO  set SCENARIO_NAME=?,POSITION_NO=?  Where SCENARIO_ID=?";
		int response=0;
		PreparedStatement stmt = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,scenario.getScenarioName());
				stmt.setInt(2,scenario.getPositionNo());
				stmt.setInt(3,scenario.getScenarioId());
				response = stmt.executeUpdate();
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		

		 	if(response==1)
		 		return true;
		 	else
		 		return false;
	}
	
	public ScenarioDTO getScenario(int scenarioId)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * MST_SCENARIO  Where SCENARIO_ID=?";
		PreparedStatement stmt = null;
		ScenarioDTO  scenario=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setInt(1,scenarioId);
				r = stmt.executeQuery();
				if (r.next())
				{
					scenario=new ScenarioDTO();
					scenario.setScenarioId(r.getInt("SCENARIO_ID"));
					scenario.setScenarioName(r.getString("SCENARIO_NAME"));
					scenario.setPositionNo(r.getInt("POSITION_NO"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return scenario;
	}
	
	public ScenarioDTO getScenario(String scenarioName)
	{
		Connection conn = ConnectionManager.getConnection();
		String sql=" Select * MST_SCENARIO  Where lower(SCENARIO_NAME)=lower(?)";
		PreparedStatement stmt = null;
		ScenarioDTO  scenario=null;
		ResultSet r = null;
			try
			{
				stmt = conn.prepareStatement(sql);

				stmt.setString(1,scenarioName);
				r = stmt.executeQuery();
				if (r.next())
				{
					scenario=new ScenarioDTO();
					scenario.setScenarioId(r.getInt("SCENARIO_ID"));
					scenario.setScenarioName(r.getString("SCENARIO_NAME"));
					scenario.setPositionNo(r.getInt("POSITION_NO"));
				}
			} 
			catch (Exception e){e.printStackTrace();}
	 		finally{try{stmt.close();ConnectionManager.closeConnection(conn);} catch (Exception e)
				{e.printStackTrace();}stmt = null;conn = null;}
	 		
	 		return scenario;
	}

}
